using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using Framework.Library.Core.Config;
using Framework.Library.Core.Data.DataEngine;
using Framework.Library.Data.DAL;
using Framework.Library.Data.IDAO;
using Framework.Library.Core.Utils.Database;
using System.Text;
using System.Linq;

namespace Framework.Library.Data.DAO.Oracle
{
    internal class TeenioContentManager : ITeenioContentManager
    {
        private Configurations cfg = new Configurations();
        #region ICMSContentManager Members

        private List<TeenioContent> Search(string text, int zoneid, string category, int portalid, int rank, int status, int type, DateTime fromdate, DateTime todate)
        {
            QueryBuilder builder = new QueryBuilder("TEENIO_CONTENTS");
            StringBuilder sb = new StringBuilder();
            if (string.IsNullOrEmpty(category))
                category = "0";
            sb.Append("SELECT top(51) * FROM " + Configurations.DATABASEOWNER + "TEENIO_CONTENTS TEENIO_CONTENTS");
            sb.Append(" WHERE TEENIO_CONTENTS.CONTENT_PORTALID = " + portalid);
            if (zoneid > 0)
                sb.Append(" AND TEENIO_CONTENTS.CONTENT_ZONEID = " + zoneid);
            if (category.IndexOf(',') > 0)
                sb.Append(" AND TEENIO_CONTENTS.CONTENT_CATEGORYID in (" + category + ")");
            else
            {
                if(category != "0")
                    sb.Append(" AND TEENIO_CONTENTS.CONTENT_CATEGORYID = " + category);
            }
            if (rank > 0)
                sb.Append(" AND TEENIO_CONTENTS.CONTENT_RANK = " + rank);
            if (status > 0)
                sb.Append(" AND TEENIO_CONTENTS.CONTENT_STATUS = " + status);
            if (type > 0)
                sb.Append(" AND TEENIO_CONTENTS.CONTENT_TYPE = " + type);            
            sb.Append(" AND TEENIO_CONTENTS.CONTENT_WAITTINGDATE between @FROM and @TO");
            var parameters = new ArrayList();
            parameters.Add(new object[] { "FROM", fromdate });
            parameters.Add(new object[] { "TO", todate });
            if (!string.IsNullOrEmpty(text))
            {
                sb.Append(" AND TEENIO_CONTENTS.CONTENT_UNUNICODE LIKE @KEYWORD");
                parameters.Add(new object[] { "KEYWORD", text });
            }
            sb.Append(" ORDER BY TEENIO_CONTENTS.CONTENT_ID DESC");
            builder.Query = sb.ToString();
            builder.Parameters = parameters;
            builder.QueryType = QueryTypes.TSql;
            return GeneralConnection.ExecuteList<TeenioContent>(CommandType.Text, builder);
        }

        public List<TeenioContent> Search(string text, int zoneid, string category, int portalid, int rank, int status, int type, DateTime fromdate, DateTime todate, int pageSize, int pageIndex, out int totalRecord)
        {
            var allList = Search(text, zoneid, category, portalid, rank, status, type, fromdate, todate).Skip(pageIndex * pageSize).Take(pageSize).ToList();
            totalRecord = allList.Count;
            return allList;
        }

        public TeenioContent GetInfo(int articleId)
        {
            QueryBuilder builder = new QueryBuilder("TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_HEADLINE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_TEASER", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_BODY", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_PREAVATAR", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_AVATAR", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_AVATARDESCRIPTION", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_SOURCE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_METADATA", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_PORTALID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CATEGORYID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ZONEID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_WAITTINGDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_MODIFYDDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_EXPIREDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEUSERNAME", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_MODIFYUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_APPROVEUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_RANK", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_TYPE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_STATUS", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ISPAY", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ISCOMMENT", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_PLAYER", "TEENIO_CONTENTS");
            builder.AddColumn("MENUID", "TEENIO_CATEGORIES");
            builder.AddTable("TEENIO_CONTENTS", new List<string>() { "CONTENT_CATEGORYID" }, "TEENIO_CATEGORIES", new List<string>() { "ZONE_ID" }, JoinTypes.InnerJoin);
            builder.AddCondition("CONTENT_ID", articleId, "TEENIO_CONTENTS");
            builder.AddSortColumn("CONTENT_ID", "TEENIO_CONTENTS", SortOrder.Descending);
            List<TeenioContent> retVal = GeneralConnection.ExecuteList<TeenioContent>(CommandType.Text, builder);
            if (retVal.Count > 0)
                return retVal[0];
            return null;
        }

        public TeenioContent GetContentIntro(int MenuId)
        {
            QueryBuilder builder = new QueryBuilder("CMS_CONTENTS");
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT TOP 1 * FROM CMS_CONTENTS WHERE CMS_CONTENTS.CONTENT_STATUS = 2 AND CMS_CONTENTS.CONTENT_WAITTINGDATE < GETDATE() AND CMS_CONTENTS.CONTENT_MENUID = @MENUID ORDER BY CMS_CONTENTS.CONTENT_WAITTINGDATE DESC");
            builder.Query = sb.ToString();
            var parameters = new ArrayList();
            parameters.Add(new object[] { "MENUID", MenuId });
            builder.Parameters = parameters;
            List<TeenioContent> retVal = GeneralConnection.ExecuteList<TeenioContent>(CommandType.Text, builder);
            if (retVal.Count > 0)
                return retVal[0];
            return null;
        }

        public List<TeenioContent> GetList(int catId, int type, TeenioContentOrder order)
        {
            QueryBuilder builder = new QueryBuilder("TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_HEADLINE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_TEASER", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_PREAVATAR", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_AVATAR", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_AVATARDESCRIPTION", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_SOURCE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_METADATA", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_PORTALID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CATEGORYID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ZONEID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_WAITTINGDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_MODIFYDDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_EXPIREDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEUSERNAME", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_MODIFYUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_APPROVEUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_RANK", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_TYPE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_STATUS", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ISPAY", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_HIT", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ISCOMMENT", "TEENIO_CONTENTS");
            builder.AddColumn("MENUID", "TEENIO_CATEGORIES");
            builder.AddTable("TEENIO_CONTENTS", new List<string>() { "CONTENT_CATEGORYID" }, "TEENIO_CATEGORIES", new List<string>() { "ZONE_ID" }, JoinTypes.InnerJoin);
            if (catId > 0)
                builder.AddCondition("CONTENT_CATEGORYID", catId, "TEENIO_CONTENTS");
            if (type > 0)
                builder.AddCondition("CONTENT_TYPE", type, "TEENIO_CONTENTS");
            switch (order)
            {
                case TeenioContentOrder.ID:
                    builder.AddSortColumn("CONTENT_ID", "TEENIO_CONTENTS", SortOrder.Descending);
                    break;
                case TeenioContentOrder.DATE:
                    builder.AddSortColumn("CONTENT_MODIFYDDATE", "TEENIO_CONTENTS", SortOrder.Descending);
                    break;
                default:
                    break;
            }
            return GeneralConnection.ExecuteList<TeenioContent>(CommandType.Text, builder);
        }
        public List<TeenioContent> GetList(int catId, int Flag, int Status, int type)
        {
            List<TeenioContent> lst = GetList(catId, type, TeenioContentOrder.ID);
            if (Flag > 0)
                lst = lst.FindAll(x => x.CONTENT_RANK == Flag);
            if (Status > 0)
                lst = lst.FindAll(x => x.CONTENT_STATUS == Status);
            return lst;
        }
        public List<TeenioContent> GetList(int catId, int Flag, int Status, int type, TeenioContentOrder order)
        {
            List<TeenioContent> lst = GetList(catId, type, order);
            if (Flag > 0)
                lst = lst.FindAll(x => x.CONTENT_RANK == Flag);
            if (Status > 0)
                lst = lst.FindAll(x => x.CONTENT_STATUS == Status);
            return lst;
        }
        public List<TeenioContent> GetList(int catId, int Flag, int Status, int type, TeenioContentOrder order, int pageIndex, int pageSize, out int totalRecord)
        {
            var allList = GetList(catId, Flag, Status, type, order).Skip(pageIndex * pageSize).Take(pageSize).ToList();
            totalRecord = allList.Count;
            return allList;
        }
        public List<TeenioContent> GetList(int catId, int Flag, int Status, int type, int pageIndex, int pageSize, out int totalRecord)
        {
            var allList = GetList(catId, Flag, Status, type).Skip(pageIndex * pageSize).Take(pageSize).ToList();
            totalRecord = allList.Count;
            return allList;
        }

        public List<TeenioContent> GetListEvent(int eventId)
        {
            QueryBuilder builder = new QueryBuilder("TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_HEADLINE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_TEASER", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_PREAVATAR", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_AVATAR", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_AVATARDESCRIPTION", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_SOURCE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_METADATA", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_PORTALID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CATEGORYID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ZONEID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_WAITTINGDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_MODIFYDDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_EXPIREDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEUSERNAME", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_MODIFYUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_APPROVEUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_RANK", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_TYPE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_STATUS", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ISCOMMENT", "TEENIO_CONTENTS");
            //builder.AddColumn("(SELECT COUNT(ID) from NS_TEENIO_CONTENTS_CONTRIBUTION where TYPE = 2 and CID = " + Configurations.DATABASEOWNER + "TEENIO_CONTENTS.CONTENT_ID)", string.Empty, "CONTENT_VIEW");
            builder.AddTable("TEENIO_CONTENTS", new List<string>() { "CONTENT_ZONEID" }, "TEENIO_PERSPECTIVE", new List<string>() { "ID" }, JoinTypes.InnerJoin);
            builder.AddCondition("TYPE", 2, "TEENIO_PERSPECTIVE");
            if (eventId > 0)
                builder.AddCondition("ID", eventId, "TEENIO_PERSPECTIVE");
            builder.AddSortColumn("CONTENT_ID", "TEENIO_CONTENTS", SortOrder.Descending);
            return GeneralConnection.ExecuteList<TeenioContent>(CommandType.Text, builder);
        }

        public List<TeenioContent> GetListEvent(int eventId, int Flag, int Status)
        {
            List<TeenioContent> lst = GetListEvent(eventId);
            if (Flag > 0)
                lst = lst.FindAll(x => x.CONTENT_RANK == Flag);
            if (Status > 0)
                lst = lst.FindAll(x => x.CONTENT_STATUS == Status);
            return lst;
        }

        public List<TeenioContent> GetListEvent(int eventId, int Flag, int Status, int pageIndex, int pageSize, out int totalRecord)
        {
            var allList = GetListEvent(eventId, Flag, Status).Skip(pageIndex * pageSize).Take(pageSize).ToList();
            totalRecord = allList.Count;
            return allList;
        }

        public List<TeenioContent> GetListHistory(int userId, int type, TeenioContentOrder order)
        {
            QueryBuilder builder = new QueryBuilder("TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_HEADLINE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_TEASER", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_PREAVATAR", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_AVATAR", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_AVATARDESCRIPTION", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_SOURCE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_METADATA", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_PORTALID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CATEGORYID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ZONEID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_WAITTINGDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_MODIFYDDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_EXPIREDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEUSERNAME", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_MODIFYUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_APPROVEUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_RANK", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_TYPE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_STATUS", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ISPAY", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_HIT", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ISCOMMENT", "TEENIO_CONTENTS");
            builder.AddColumn("MENUID", "TEENIO_CATEGORIES");
            builder.AddTable("TEENIO_CONTENTS", new List<string>() { "CONTENT_CATEGORYID" }, "TEENIO_CATEGORIES", new List<string>() { "ZONE_ID" }, JoinTypes.InnerJoin);
            builder.AddTable("TEENIO_CONTENT_HISTORY", new List<string>() { "CONTENT_ID" }, "TEENIO_CONTENT_HISTORY", new List<string>() { "AUDIOID" }, JoinTypes.InnerJoin);
            if (type > 0)
                builder.AddCondition("CONTENT_TYPE", type, "TEENIO_CONTENTS");
            switch (order)
            {
                case TeenioContentOrder.ID:
                    builder.AddSortColumn("CONTENT_ID", "TEENIO_CONTENTS", SortOrder.Descending);
                    break;
                case TeenioContentOrder.DATE:
                    builder.AddSortColumn("CONTENT_MODIFYDDATE", "TEENIO_CONTENTS", SortOrder.Descending);
                    break;
                default:
                    break;
            }
            return GeneralConnection.ExecuteList<TeenioContent>(CommandType.Text, builder);
        }

        public List<TeenioContent> GetListHistory(int userId, int type, TeenioContentOrder order, int pageIndex, int pageSize, out int totalRecord)
        {
            var allList = GetListHistory(userId, type, order);
            totalRecord = allList.Count;
            allList = allList.Skip(pageIndex * pageSize).Take(pageSize).ToList();
            return allList;
        }

        public List<TeenioContent> GetListUser(int userId, int type, TeenioContentOrder order)
        {
            QueryBuilder builder = new QueryBuilder("TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_HEADLINE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_TEASER", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_PREAVATAR", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_AVATAR", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_AVATARDESCRIPTION", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_SOURCE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_METADATA", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_PORTALID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CATEGORYID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ZONEID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_WAITTINGDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_MODIFYDDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_EXPIREDATE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_CREATEUSERNAME", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_MODIFYUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_APPROVEUSERID", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_RANK", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_TYPE", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_STATUS", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ISPAY", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_HIT", "TEENIO_CONTENTS");
            builder.AddColumn("CONTENT_ISCOMMENT", "TEENIO_CONTENTS");
            builder.AddColumn("MENUID", "TEENIO_CATEGORIES");
            builder.AddTable("TEENIO_CONTENTS", new List<string>() { "CONTENT_CATEGORYID" }, "TEENIO_CATEGORIES", new List<string>() { "ZONE_ID" }, JoinTypes.InnerJoin);
            if (userId > 0)
                builder.AddCondition("CONTENT_CREATEUSERID", userId, "TEENIO_CONTENTS");
            if (type > 0)
                builder.AddCondition("CONTENT_TYPE", type, "TEENIO_CONTENTS");
            switch (order)
            {
                case TeenioContentOrder.ID:
                    builder.AddSortColumn("CONTENT_ID", "TEENIO_CONTENTS", SortOrder.Descending);
                    break;
                case TeenioContentOrder.DATE:
                    builder.AddSortColumn("CONTENT_MODIFYDDATE", "TEENIO_CONTENTS", SortOrder.Descending);
                    break;
                default:
                    break;
            }
            return GeneralConnection.ExecuteList<TeenioContent>(CommandType.Text, builder);
        }

        public List<TeenioContent> GetListUser(int userId, int type, TeenioContentOrder order, int pageIndex, int pageSize, out int totalRecord)
        {
            var allList = GetListUser(userId, type, order);
            totalRecord = allList.Count;
            allList = allList.Skip(pageIndex * pageSize).Take(pageSize).ToList();
            return allList;
        }
        #endregion
    }
}